package com.dwjm.shop.Jbp.Goods;

import com.dwjm.shop.Jbp.utils.ConnectionDatabaseForSQLSeverUseJdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class GoodsDaoImpl implements GoodsDao {
    Connection connection = null;

    PreparedStatement pstmt = null;
    ResultSet rset = null;

    public GoodsDaoImpl() {
        connection = ConnectionDatabaseForSQLSeverUseJdbc.getConnection();
    }

    @Override
    public int addGoods(Goods goods) {
        try {
            pstmt = connection.prepareStatement(
                    "insert into t_goods(title, price, descr, img, state, mid) values(?, ?, ?, ?, ?, ?)");
            pstmt.setString(1, goods.getTitle());
            pstmt.setBigDecimal(2, goods.getPrice());
            pstmt.setString(3, goods.getDescr());
            pstmt.setString(4, goods.getImg());
            pstmt.setInt(5, 0);
            pstmt.setInt(6, goods.getMid());
            int i = pstmt.executeUpdate();
            return i == 1 ? 1 : 0; // 三目运算符
        } catch (Exception exception) {
            exception.printStackTrace();
        }
        return 0;

    }

    @Override
    public List<Goods> findAllGoods(int page, int size) {
        List<Goods> list = new ArrayList<>();
        try {
            // 验证数据库连接状态
            if (connection == null || connection.isClosed()) {
                connection = ConnectionDatabaseForSQLSeverUseJdbc.getConnection();
            }

            // 使用SQL Server 2008兼容的分页查询语法
            pstmt = connection.prepareStatement(
                    "WITH OrderedGoods AS (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM t_goods) " +
                            "SELECT * FROM OrderedGoods WHERE RowNum BETWEEN ? AND ? ORDER BY RowNum");
            int start = (page - 1) * size + 1;
            int end = page * size;
            pstmt.setInt(1, start);
            pstmt.setInt(2, end);
            rset = pstmt.executeQuery();

            Goods goods = null;
            while (rset.next()) {
                goods = new Goods(
                        rset.getInt("id"),
                        rset.getString("title"),
                        rset.getBigDecimal("price"),
                        rset.getString("descr"),
                        rset.getString("img"),
                        rset.wasNull() ? 0 : rset.getInt("state"),
                        rset.wasNull() ? 0 : rset.getInt("mid"));

                list.add(goods);
            }
            return list;

        } catch (Exception e) {
            System.err.println("分页查询商品失败 - 页码: " + page + ", 每页大小: " + size + ", 错误: " + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException("数据库查询失败", e);
        } finally {
            try {
                if (rset != null)
                    rset.close();
                if (pstmt != null)
                    pstmt.close();
            } catch (Exception e) {
                System.err.println("资源关闭错误: " + e.getMessage());
            }
        }
    }

    @Override
    public int deleteGoods(Integer id) {
        try {
            pstmt = connection.prepareStatement("update  t_goods set state=1 where id = ?");
            pstmt.setInt(1, id);
            int i=pstmt.executeUpdate();
            return i ==1?1:0;

        }catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public List<Goods> findGoodsByKeyword(String keyword) {
        List<Goods> list = new ArrayList<>();
        try {

            pstmt = connection.prepareStatement("select  id, title, price, descr, img, state, mid from t_goods where title like ? ");
            pstmt.setString(1, "%" + keyword + "%");
            rset = pstmt.executeQuery();
            Goods goods = null;
            while (rset.next()) {
                goods = new Goods(
                        rset.getInt("id"),
                        rset.getString("title"),
                        rset.getBigDecimal("price"),
                        rset.getString("descr"),
                        rset.getString("img"),
                        rset.wasNull() ? 0 : rset.getInt("state"),
                        rset.wasNull() ? 0 : rset.getInt("mid"));
                list.add(goods);
            }
            return list;
        }catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

}
